Homework 6 - Creating a resume database

Author

Christopher Torres

Published

April 7, 2025

This Homework, we will be walking through the steps it takes to connect to a database, and then produce equations on this database using SQL and SQL functions we developed in the My-DDL.SQL file. Both that file and this one will be helpful to understand what is taking place. We are going to be manipulating our HR database by editing the People, Skills, and Peopleskills tables! We will walk through each table, before performing filters and functions on them in the Queries section below! Welcome to Homework 4!

GITHUB URL: https://github.com/cmsc-vcu/cmsc408-sp2025-hw6-serrotrehpotsirhc.git

Checking env variables:
CMSC408_HW6_USER = sp25_torresc6
CMSC408_HW6_PASSWORD = Shout4_torresc6_joY
CMSC408_HW6_HOST = cmsc-vcu.com
CMSC408_HW6_DB_NAME = sp25_torresc6_hr

Overview and description

The database will represent a company-wide resume, capturing the collective skills and certifications of all the people in your company. The database will capture skills, employees and their roles within the company. The database will help our employer find employees with specific skills, list the skills of any employee and help find gaps in the skill sets of employees in specific roles.

Crows-foot diagram

This is the Crows Foot diagram for this database. People have skills. People can have many skills, and skills can each have many people. Both People and Skills are each One to Many toward each other, making both Many to Many.

erDiagram
    people ||--o{ peopleskills : has
    skills ||--o{ peopleskills : includes

    people {
        int id PK
        string first_name
        string last_name
        string email
        string linkedin_url
        string headshot_url
        string discord_handle
        string brief_bio
        date date_joined
    }

    skills {
        int id PK
        string name
        string description
        string tag
        string url
        string extra
    }

    peopleskills {
        int id PK
        int skills_id FK
        int people_id FK
        datetime date_acquired
    }

Examples of data in the database

The following sections provide an overview of the schema including table names, and number of rows and columns in each table.

Each table is presented along with a description of it’s contents.

Tables and metrics in the database

TableName RecordCount ColumnCount
countries 25.0 3
departments 27.0 4
emp_details_view NULL 16
employees 107.0 11
job_grades 6.0 3
job_history 10.0 5
jobs 19.0 4
locations 23.0 6
people 10.0 9
peopleskills 26.0 4
regions 4.0 2
skills 8.0 6
Total Rows: 12, Total Columns: 3

People table

The people table contains a SQL Select call to print the entire people table, using the *.

Below is a list of data in the people table.

id first_name last_name email linkedin_url headshot_url discord_handle brief_bio date_joined
1 Avery Person 1 avery1@example.com https://linkedin.com/in/avery1 https://example.com/headshots/avery.jpg @avery01 Fitness enthusiast and tech blogger. 2025-01-01
2 Jordan Person 2 jordan2@example.com https://linkedin.com/in/jordan2 https://example.com/headshots/jordan.jpg @jordan02 Mechanical engineer who loves extreme sports. 2025-01-01
3 Taylor Person 3 taylor3@example.com https://linkedin.com/in/taylor3 https://example.com/headshots/taylor.jpg @taylor03 Writer and hobbyist swimmer. 2025-01-01
4 Morgan Person 4 morgan4@example.com https://linkedin.com/in/morgan4 https://example.com/headshots/morgan.jpg @morgan04 Artist focused on sustainable designs. 2025-01-01
5 Riley Person 5 riley5@example.com https://linkedin.com/in/riley5 https://example.com/headshots/riley.jpg @riley05 Racing enthusiast and cliff diver. 2025-01-01
6 Skylar Person 6 skylar6@example.com https://linkedin.com/in/skylar6 https://example.com/headshots/skylar.jpg @skylar06 Tech consultant with a passion for flying. 2025-01-01
7 Casey Person 7 casey7@example.com https://linkedin.com/in/casey7 https://example.com/headshots/casey.jpg @casey07 Athlete and mural enthusiast. 2025-01-01
8 Quinn Person 8 quinn8@example.com https://linkedin.com/in/quinn8 https://example.com/headshots/quinn.jpg @quinn08 Entrepreneur and adventure lover. 2025-01-01
9 Reese Person 9 reese9@example.com https://linkedin.com/in/reese9 https://example.com/headshots/reese.jpg @reese09 Freelancer and swimmer. 2025-01-01
10 Parker Person 10 parker10@example.com https://linkedin.com/in/parker10 https://example.com/headshots/parker.jpg @parker10 Strength coach and motivational speaker. 2025-01-01
Total Rows: 10, Total Columns: 9

Skills table

The skills table contains a SQL Select call to print the entire skills table, using the *.

Below is a list of data in the skills table.

id name description tag url time_commitment
1 Rope Jumping Jumping Rope Skill 1 www.google.com NULL
2 Sky Diving Jumping Rope Skill 2 www.google.com NULL
3 Race Car Driving Jumping Rope Skill 3 www.google.com NULL
4 Heavy Lifting Jumping Rope Skill 4 www.google.com NULL
5 Swimmer Jumping Rope Skill 5 www.google.com NULL
6 Cliff Jumping Jumping Rope Skill 6 www.google.com NULL
7 Mural Artist Jumping Rope Skill 7 www.google.com NULL
8 Author Jumping Rope Skill 8 www.google.com NULL
Total Rows: 8, Total Columns: 6

PeopleSkills table

The peopleskills table contains a SQL Select call to print the entire peopleskils table, using the *.

Below is a list of data in the peopleskills table.

id skills_id people_id date_acquired
1 1 1 2024-01-01
2 3 1 2024-01-01
3 6 1 2024-01-01
4 3 2 2024-01-01
5 4 2 2024-01-01
6 5 2 2024-01-01
7 1 3 2024-01-01
8 5 3 2024-01-01
9 3 5 2024-01-01
10 6 5 2024-01-01
11 2 6 2024-01-01
12 3 6 2024-01-01
13 4 6 2024-01-01
14 3 7 2024-01-01
15 5 7 2024-01-01
16 6 7 2024-01-01
17 1 8 2024-01-01
18 3 8 2024-01-01
19 5 8 2024-01-01
20 6 8 2024-01-01
21 2 9 2024-01-01
22 5 9 2024-01-01
23 6 9 2024-01-01
24 1 10 2024-01-01
25 4 10 2024-01-01
26 5 10 2024-01-01
Total Rows: 26, Total Columns: 4

Queries

List skill id, name and tag ordered by name

id name tag
8 Author Skill 8
6 Cliff Jumping Skill 6
4 Heavy Lifting Skill 4
7 Mural Artist Skill 7
3 Race Car Driving Skill 3
1 Rope Jumping Skill 1
2 Sky Diving Skill 2
5 Swimmer Skill 5
Total Rows: 8, Total Columns: 3

List people names and email addresses ordered by last_name

first_name last_name email
Avery Person 1 avery1@example.com
Parker Person 10 parker10@example.com
Jordan Person 2 jordan2@example.com
Taylor Person 3 taylor3@example.com
Morgan Person 4 morgan4@example.com
Riley Person 5 riley5@example.com
Skylar Person 6 skylar6@example.com
Casey Person 7 casey7@example.com
Quinn Person 8 quinn8@example.com
Reese Person 9 reese9@example.com
Total Rows: 10, Total Columns: 3

List skill names of Person 1

name
Rope Jumping
Race Car Driving
Cliff Jumping
Total Rows: 3, Total Columns: 1

List people names with Skill 6

first_name last_name
Avery Person 1
Riley Person 5
Casey Person 7
Quinn Person 8
Reese Person 9
Total Rows: 5, Total Columns: 2

List names and email addresses of people without skills

first_name last_name email
Morgan Person 4 morgan4@example.com
Total Rows: 1, Total Columns: 3

List names and tags of unused skills

name tag
Mural Artist Skill 7
Author Skill 8
Total Rows: 2, Total Columns: 2

Reflection

This assignment took forever! It took forever for me to get connected to the database. I had to run .env and make sure the script was picking up the credentials. Then I found out it was related to an encoding issue I ran into when I accidently produced a file from the SQLTools generated connection. I cleaned up my mistakes after a while, and finally got into the DDL and SQL coding! This took up so much time, and I watched both lectures!

The lectures jump straight to the assignment with no server connectivity issue. It was a whole thing for me! However, after getting it all worked out, it was a satisfying practice with SQL Tools, and I am looking forward to our final assignments. I am going to get into contact with my team soon to set up a way to start thinking of how we are going to implement our country club database. Thank you!

CMSC408 - Homework 6 - Spring 2025

Welcome to Homework 4! Here I am going to break down the summary, purpose of this assginment, and which files we will be editing! Thank you!

Summary

Homework 6 is all about working with a databse using SQL, and then creating an HTML rendered by Quarto, which performs functions on this database using SQL inside of python code.

Where are the important files

We store our QMD inside the reports folder. Report.QMD holds the python code which we generate into HTML. Inside of the Python, we hold snippets of SQL, using the functions we defined in helpers.py. These SQL pieces manipulate, filter, and extract our database.

Database Construction

We edited the database we have connected to through our remote poetry connection, through the MY-DDL File, which is where we populate and create each table of the database we are working with today!

Installations

We have installed poetry. Specifically for this homework, I didn’t need to install anything.

Running

We run this assignment through rendering the quarto, which interacts with the database through a remote connection. After, establishing connection, this Report.QMD file is converted to HTML, and it runs the Python code, which interacts with the database. We populated this database beforehand in My-DDL.SQL!

Thank you! That is all! Really frusturating assignment, but glad it worked out!